if exists (select * from sysobjects where type='P' and name='RequirementAnalysis') 
	drop procedure RequirementAnalysis
go

-- Requirement analysis report
-- Match on Feb 2, 2015
create procedure RequirementAnalysis
	 @conditionType int=0
	,@materialCode varchar(20)=null
	,@produceConducts varchar(max)=null
as begin
	begin try
		declare @errorMessage nvarchar(max),@errorServerity int,@errorState int;

		set fmtonly off;

		select distinct
			 a1.ProduceConductId
			,a1.Code ProduceConductCode
			,a1.ProcurementOrderId
			,a1.BatchAmount
			,d1.MaterialId
			,d1.SingleCosts
		into #ProduceConductData
		from ProduceConduct a1
		inner join Product b1 on b1.ProductId=a1.ProductId
		inner join BOM c1 on c1.Code=b1.Code
		inner join BOMDetails d1 on d1.BOMId=c1.BOMId
		where (@produceConducts is null or a1.Code in(select * from dbo.Func_Split(@produceConducts,',')));

		select distinct
			 a.Code
			,a.Name
			,ISNULL((select SUM(Quantity) from MaterialStore where MaterialId=a.MaterialId),0) StoreAmount
			,ISNULL((select SUM(Quantity-InStoreQuantity) 
					 from ProcurementOrderDetails a1
					 inner join ProcurementOrder b1 on b1.ProcurementOrderId=a1.ProcurementOrderId
					 where a1.MaterialId=a.MaterialId and (@produceConducts is null 
						or b1.ProcurementOrderId in(select ProcurementOrderId from #ProduceConductData))),0) ProcurementAmount
			,ISNULL((select SUM(BatchAmount*SingleCosts)
					 from #ProduceConductData
					 where MaterialId=a.MaterialId),0) ProduceAmount
			,a.PreserveAmount
			,ISNULL((select ProduceConductCode+',' from #ProduceConductData where MaterialId=a.MaterialId for xml path('')),'') ProduceConducts
		into #MaterialData
		from Material a
		where (@materialCode is null or a.Code like '%'+ @materialCode +'%') and @produceConducts is null
			or a.MaterialId in(select MaterialId from #ProduceConductData);

		select distinct 
			 *
			,ISNULL(StoreAmount-PreserveAmount,0) NetStockAmout
			,ISNULL((StoreAmount+ProcurementAmount)-(ProduceAmount+PreserveAmount),0) ShortageAmount
		from #MaterialData
		where @conditionType=0
			or @conditionType=1 and StoreAmount<PreserveAmount
			or @conditionType=2 and StoreAmount+ProcurementAmount<ProduceAmount+PreserveAmount;
	end try
	begin catch
		select @errorMessage=ERROR_MESSAGE(),@errorServerity=ERROR_SEVERITY(),@errorState=ERROR_STATE();
		raiserror (@errorMessage,@errorServerity,@errorState);
	end catch
end

--exec RequirementAnalysis 0,null,'W15012405'